﻿-- =============================================
-- Script Template
-- =============================================
USE [host8446263]
GO

/****** Object:  StoredProcedure [dbo].[P_TrendChart_SSQ_DX]    Script Date: 07/26/2012 23:29:48 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [P_TrendChart_SSQ_DX] --双色球大小分析图

AS

set nocount on

Create table #LotteryNumberListSSQ(id int identity(1,1),Isuse varchar(20),RQ_0 varchar(80),
		BQ_0 VARCHAR(20),H_Z INT,RZ_0 int,RZ_1 int,RZ_2 int,RZ_3 int,RZ_4 int,RZ_5 int,RZ_6 int,
		BZ_0 VARCHAR(20),BH_0 VARCHAR(20),R1Z_0 INT,R1H_0 INT,R2Z_0 INT,R2H_0 INT,
		R3Z_0 INT,R3H_0 INT,R4Z_0 INT,R4H_0 INT,R5Z_0 INT,R5H_0 INT,R6Z_0 INT,R6H_0 INT,
		RHZ_0 INT,RHH_0 INT,Z_H VARCHAR(20))

declare @cur cursor 
declare @Isuse varchar(20), @WinLotteryNumber varchar(100), @id int 

DECLARE @RQ_0 varchar(80),@BQ_0 VARCHAR(20),@H_Z INT,@RZ_0 int,@RZ_1 int,@RZ_2 int,@RZ_3 int,@RZ_4 int,@RZ_5 int,@RZ_6 int,
		@BZ_0 VARCHAR(20),@BH_0 VARCHAR(20),@R1Z_0 INT,@R1H_0 INT,@R2Z_0 INT,@R2H_0 INT,@R3Z_0 INT,@R3H_0 INT,@R4Z_0 INT,
		@R4H_0 INT,@R5Z_0 INT,@R5H_0 INT,@R6Z_0 INT,@R6H_0 INT,@RHZ_0 INT,@RHH_0 INT,@Z_H VARCHAR(20)

DECLARE @QW int,@BW int,@SW int,@W int,@Q INT,@B INT,@S INT,@G INT,@Z_0 INT,@H_0 INT,@R_Z_G INT

Create table #tb(id int,PlayNo varchar(20),WinLotteryNumber varchar(100))

		insert into #tb select Id,PlayNo, WinLotteryNumber from pm_plays  with(nolock) where WinLotteryNumber <> '' and LotteryID = 5 order by [EndTime] asc

set @cur = cursor FAST_FORWARD for select PlayNo,WinLotteryNumber from #tb 

open @cur

fetch next from @cur into @Isuse, @WinLotteryNumber

while @@fetch_status=0
begin

SET @Z_0 = 0 SET @H_0 = 0 SET @R_Z_G = 0 set @RHZ_0 = 0 set @RHH_0 = 0 set @BZ_0 = 0 set @BH_0 = 0
SET @RZ_0 = -1 SET @RZ_1 = -1 SET @RZ_2 = -1 SET @RZ_3 = -1 SET @RZ_4 = -1 SET @RZ_5 = -1 SET @RZ_6 = -1
set @R1Z_0 = 0 set @R1H_0 = 0 set @R2Z_0 = 0 set @R2H_0 = 0 set @R3Z_0 = 0 set @R3H_0 = 0 set @R4Z_0 = 0 
set @R4H_0 = 0 set @R5Z_0 = 0 set @R5H_0 = 0 set @R6Z_0 = 0 set @R6H_0 = 0

set @QW = Substring(@WinLotteryNumber,1,2) 
set @BW = Substring(@WinLotteryNumber,4,2) 
set @SW = Substring(@WinLotteryNumber,7,2)
set @W = Substring(@WinLotteryNumber,10,2) 
set @Q = Substring(@WinLotteryNumber,13,2)
set @B = Substring(@WinLotteryNumber,16,2) 
set @S = Substring(@WinLotteryNumber,21,2)-----蓝球号码
set @G = Substring(@WinLotteryNumber,24,2)-----快乐星期天号码

set @RQ_0 = SUBSTRING(@WinLotteryNumber,1,17) 
SET @BQ_0 = SUBSTRING(@WinLotteryNumber,21,2) 
SET @H_Z = @QW + @BW + @SW + @W + @Q + @B

------红球大码
IF(16 < @QW and @QW < 34)  
BEGIN
	SET @R_Z_G =@R_Z_G + 1
END

IF(16 < @BW and @BW < 34)  
BEGIN
	SET @R_Z_G =@R_Z_G + 1
END

IF(16 < @SW and @SW < 34)  
BEGIN
	SET @R_Z_G =@R_Z_G + 1
END

IF(16 < @W and @W < 34)  
BEGIN
	SET @R_Z_G =@R_Z_G + 1
END

IF(16 < @Q and @Q < 34)  
BEGIN
	SET @R_Z_G =@R_Z_G + 1
END

IF(16 < @B and @B < 34)  
BEGIN
	SET @R_Z_G =@R_Z_G + 1
END

IF(@R_Z_G = 0)
BEGIN
	SET @RZ_0 = 0
END

IF(@R_Z_G = 1)
BEGIN
	SET @RZ_1 = 1
END

IF(@R_Z_G = 2)
BEGIN
	SET @RZ_2 = 2
END

IF(@R_Z_G = 3)
BEGIN
	SET @RZ_3 = 3
END

IF(@R_Z_G = 4)
BEGIN
	SET @RZ_4 = 4
END

IF(@R_Z_G = 5)
BEGIN
	SET @RZ_5 = 5
END

IF(@R_Z_G = 6)
BEGIN
	SET @RZ_6 = 6
END

----蓝求大小
IF(0 < @S and @S < 9)
BEGIN
	SET @BZ_0 =@S
END
ELSE
	BEGIN
		SET @BH_0 =@S
	END
------红球大小
IF(0 < @QW and @QW < 17)
BEGIN
	SET @R1Z_0 =@QW
END
ELSE
	BEGIN
		SET @R1H_0 =@QW
	END

IF(0 < @BW and @BW < 17)
BEGIN
	SET @R2Z_0 =@BW
END
ELSE
	BEGIN
		SET @R2H_0 =@BW
	END

IF(0 < @SW and @SW < 17)
BEGIN
	SET @R3Z_0 =@SW
END
ELSE
	BEGIN
		SET @R3H_0 =@SW
	END

IF(0 < @W and @W < 17)
BEGIN
	SET @R4Z_0 =@W
END
ELSE
	BEGIN
		SET @R4H_0 =@W
	END

IF(0 < @Q and @Q < 17)
BEGIN
	SET @R5Z_0 =@Q
END
ELSE
	BEGIN
		SET @R5H_0 =@Q
	END

IF(0 < @B and @B < 17)
BEGIN
	SET @R6Z_0 =@B
END
ELSE
	BEGIN
		SET @R6H_0 =@B
	END

----临时变量存放尾和
DECLARE @H_Z_W VARCHAR(10)

SET @H_Z_W =RTRIM(LTRIM(STR(@H_Z)))

IF(LEN(@H_Z)=3)
BEGIN
	if(0 < substring(@H_Z_W,3,1) and substring(@H_Z_W,3,1) < 5)
		begin
			set @RHZ_0 = substring(@H_Z_W,3,1)
		end
	else
		begin
			set @RHH_0 =substring(@H_Z_W,3,1)
		end
END

ELSE
BEGIN
	if(0 < substring(@H_Z_W,2,1) and substring(@H_Z_W,2,1) < 5)
		begin
			set @RHZ_0 = substring(@H_Z_W,2,1)
		end
	else
		begin
			set @RHH_0 =substring(@H_Z_W,2,1)
		end
END
----------针对红球的大小比

set @Z_0 =@R_Z_G
set @H_0 = 6 - @R_Z_G

SET @Z_H =RTRIM(LTRIM(STR(@H_0))) +' : '+ RTRIM(LTRIM(STR(@Z_0))) 

insert into #LotteryNumberListSSQ values (@Isuse,@RQ_0 ,@BQ_0 ,@H_Z ,@RZ_0 ,@RZ_1 ,@RZ_2 ,@RZ_3 ,@RZ_4 ,@RZ_5 ,@RZ_6 ,
		@BZ_0 ,@BH_0 ,@R1Z_0 ,@R1H_0 ,@R2Z_0 ,@R2H_0 ,@R3Z_0 ,@R3H_0 ,@R4Z_0 ,@R4H_0 ,@R5Z_0 ,@R5H_0 ,@R6Z_0 ,@R6H_0 ,@RHZ_0 ,@RHH_0 ,@Z_H )
		
fetch next from @cur into @Isuse, @WinLotteryNumber

end

close @cur

select * from #LotteryNumberListSSQ


set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON


GO


